IF OBJECT_ID('dbo.fnGetSecurityAgreements') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetSecurityAgreements
    IF OBJECT_ID('dbo.fnGetSecurityAgreements') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetSecurityAgreements >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetSecurityAgreements >>>'
END
go

-- =======================================================================
-- Function Name: dbo.fnGetSecurityAgreements()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 12/08/2008
--
-- Description: Returns Table of Agreements, including AgreementId, 
--             InfoEdPropNumber and FolderNumber, that the given user
--             is allowed to see. Uses ResourceIdtype directly attached
--             to the Agreement (1), and via the FundId (4)
--
-- Parameters:  @DomainUserId
--              
-- Returns:     TABLE (see defn below) 
--
-- SampleCall:
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 12/08/08    KC        Created
--
-- =======================================================================
CREATE FUNCTION dbo.fnGetSecurityAgreements
(
     @DomainUserId      INT
)
RETURNS @UserAgreements TABLE
(
     AgreementId              INT,
     FolderNumber             VARCHAR(12),
     InfoEdPropNumber         VARCHAR(50)
)
AS
BEGIN
     INSERT    @UserAgreements
               (AgreementId,
               FolderNumber,
               InfoEdPropNumber)
     SELECT    DISTINCT AgreementId,
               FolderNumber,
               InfoEdPropNumber
     FROM      (
               -- ResourceTypeId = 1, Direct link to Agreements
               SELECT    AUTH.ResourceId,
                         AI.AgreementId,
                         AI.FolderNumber,
                         AI.InfoEdPropNumber
               FROM      dbo.UserResourceCache AUTH
                           INNER JOIN dbo.AgrAgreement_Info AI ON AUTH.ResourceId = AI.ResourceId 
                                   AND AUTH.ResourceTypeId = 1
               WHERE     AUTH.FwkDomainUserId = @DomainUserId 
               UNION
               -- ResourceTypeId = 4, Link to FundId, then link to Agreements
               SELECT    AUTH.ResourceId,
                         AI.AgreementId,
                         AI.FolderNumber,
                         AI.InfoEdPropNumber
               FROM      dbo.UserResourceCache AUTH
                           INNER JOIN dbo.AgrFund F ON AUTH.ResourceId = F.FwkResourceId 
                                   AND AUTH.ResourceTypeId = 4                                
                           INNER JOIN dbo.AgrFund_AgrAgreement_Map M ON F.Id = M.AgrFundId
                           INNER JOIN dbo.AgrAgreement_Info AI ON M.AgrAgreementId = AI.AgreementId 
               WHERE     AUTH.FwkDomainUserId = @DomainUserId ) 
     AGRLIST
     
     RETURN
END
go
IF OBJECT_ID('dbo.fnGetSecurityAgreements') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetSecurityAgreements >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetSecurityAgreements >>>'
go